# -*- coding:UTF-8 -*-
import decimal
import json
from openpyxl.styles.fills import FILL_PATTERN_LIGHTTRELLIS
import requests
import time
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font

from db import *
from spider_xue.logger import logging
from spider_xue.settings import DEBUG, INFO, ERROR, BREAK_FLAG, KEYS, ding_url1, HOT, ding_url2


mg = {
    "msgtype": "markdown",
    "markdown": {
        "title": "发言",
        "text": "#### NGAxx发言 \n> 9度，西北风1级，空气良89，相对温度73%\n #### 禾佬发言 \n> 10点20分发布 [天气](https://www.dingtalk.com) \n"
    },
    "at": {
        "atMobiles": [],
        "isAtAll": False
    }
}
dao = ['SH603722', 'SZ002590', 'SH601778', 'SZ002316']

headers = {
    'Content-Type': 'application/json'
}

baidu = "https://www.baidu.com/s?wd="


def send_msg(msg):
    mg["markdown"]["text"] = msg
    time.sleep(0.5)
    requests.post(ding_url1, data=json.dumps(mg), headers=headers)


def send_s_msg(x, msg, url, dates_msg=''):

    name = x['name']
    symbol = x['symbol']
    url1 = f"https://wap.eastmoney.com/quote/stock/{symbol.replace('SH', '1.').replace('SZ', '0.')}.html"
    # url1 = f'http://m.10jqka.com.cn/stockpage/hs_{symbol[2:]}'
    if dates_msg:
        msg = f'{dates_msg} \n {msg}'
    if symbol in dao:
        # mg['at']['atMobiles'].append('17379042291')
        mg['at']['isAtAll'] = True
    if x:
        # if x.get('ths_hxtc', ''):
        #     ths_hxtc = x.get('ths_hxtc', '').split(' ')
        #     ttttt = []
        #     for i in ths_hxtc:
        #         if i in HOT:
        #             ttttt.append(i)
        #     if ttttt:
        #         msg = f'{" ".join(ttttt)} \n {msg}'
        #         url = ding_url2
        if x.get('macd') and x.get('macd') > -2:
            mg["markdown"]["text"] = f"""### [{symbol}{name} MACD{x.get('macd')}]({url1}) \n {msg} \n - 同花顺:{x.get('ths_hxtc', '')[:151]} \n - 题材概念:{x['hxtc'][:151]} \n - 经营:{x['jyfw'][:151]} \n - 简介:{x['gsjj'][:151]}"""
        else:
            mg["markdown"]["text"] = f"""### [{symbol}{name}]({url1}) \n {msg} \n - 同花顺:{x.get('ths_hxtc', '')[:151]} \n - 题材概念:{x['hxtc'][:151]} \n - 经营:{x['jyfw'][:151]} \n - 简介:{x['gsjj'][:151]}"""
    else:
        mg["markdown"]["text"] = f"{symbol} {msg} "
    requests.post(url, data=json.dumps(mg), headers=headers)


def check_data_and_stock():
    stocks = stock_db.find({"use": 1})
    x = [i for i in stocks]
    print(len(x))
    stock2 = history_five_db.find()
    z = [i['symbol'] for i in stock2]
    lll = []
    for u in x:
        if u['symbol'] not in z:
            print(u)
            lll.append(u)
    print(len(lll))


def reset_all_account():
    accounts = account_db.find()
    count = 0
    for account in accounts:
        # print(account)
        account_db.update_one({'phone': account['phone']}, {"$set": {"is": 0}})
        count += 1

    if count < 7:
        send_msg("账号有点太少了把！！！！%s" % count)


def reset_one_account():
    account = account_db.find_one()
    account_db.update_one({'phone': account['phone']}, {"$set": {"is": 0}})


def reset_all_ip():
    tmps = new_ip_db.find()
    count = 0
    for tmp in tmps:
        new_ip_db.update_one({"ip": tmp["ip"]}, {
                             "$set": {"status": 1, "count": 3}})
        count += 1
    if count < 3:
        send_msg("IPIP有点太少了把！！！！%s" % count)
        raise ValueError("ip 太少了")


def break_flag():
    t = time.localtime(time.time())
    if BREAK_FLAG and ((t.tm_hour == 9 and t.tm_min < 31) or (11 == t.tm_hour and 33 < t.tm_min) or (12 == t.tm_hour) or (t.tm_hour == 15 and t.tm_min > 5)):
        return True

# reset_all_account()
# reset_all_ip()
# reset_one_account()


def get_day_not_today():
    f = stock_db.find({"use": 1})
    cc = []
    for sto in f:
        d = data_day_db.find_one({"symbol": sto['symbol']})
        it = d['item']
        if it[-1][0] != (settings_db.find_one({"name": "nine_timestamp"})['timestamp'] - 34200) * 1000:
            print(sto['symbol'], time.strftime(
                "%Y--%m--%d %H:%M:%S", time.localtime(int(it[-1][0]/1000))))
            cc.append(sto)
    return cc


def get_five_not_today():
    f = stock_db.find({"use": 1})
    cc = []
    for sto in f:
        d = history_five_db.find_one({"symbol": sto['symbol']})
        it = d['five_time_list']
        if it[-1] != "2022-01-07":
            print(sto['symbol'], d['five_time_list'][-3:])
            cc.append(sto)
            continue

        # ct = 0
        # for dd in it[-10:]:
        #     if dd[7] < 0.5:
        #         ct += 1
        # if ct >= 7:
        #     stock_db.update_one({"symbol": sto['symbol']}, {"$set": {"today": 1}})
        #     continue
        # cc += 1
        # print(cc)
    return cc


def sset3():
    f = stock_db.find({"today": 2})
    cc = 0
    for sto in f:
        stock_db.update_one({"symbol": sto['symbol']}, {"$set": {"today": 2}})
        msg = sto['gsjj'] + sto['hxtc'] + sto['jyfw']
        f = 0
        for k in KEYS:
            if k in msg:
                f = 1
                break
        if not f:
            stock_db.update_one({"symbol": sto['symbol']}, {
                                "$set": {"today": 1}})


def sset4():
    f = stock_db.find({"use": 1})
    cc = []
    al = 0
    for sto in f:
        d = data_one_db.find_one({"symbol": sto['symbol']})
        it = d['item']
        if it[-1][0] == 1635404400000:

            if sto['today'] != 2:
                d = data_day_db.find_one({"symbol": sto['symbol']})
                if d['item'][-1][7] > -9:
                    msg = sto['gsjj'] + sto['hxtc'] + sto['jyfw']
                    f = 0
                    for k in KEYS:
                        if k in msg:
                            f = 1
                            break
                    print(sto['symbol'], time.strftime(
                        "%Y--%m--%d %H:%M:%S", time.localtime(int(it[-1][0]/1000))))
                    if f:

                        cc.append((sto['symbol'], sto['name']))

            # stock_db.update_one({"symbol": sto['symbol']}, {"$set": {"today": 1}})
        al += 1
        # ct = 0
        # for dd in it[-10:]:
        #     if dd[7] < 0.5:
        #         ct += 1
        # if ct >= 7:
        #     stock_db.update_one({"symbol": sto['symbol']}, {"$set": {"today": 1}})
        #     print(ct, sto['name'])
        #     continue
        # cc += 1
    print(cc, al)
    return cc


llo = ['融资融券', '转融券标的', '标普道琼斯A股', 'MSCI概念', '沪股通', '深股通', '同花顺漂亮100']


def ths_res(code):
    ths_tcgn = ''
    try:
        ths_url = "http://basic.10jqka.com.cn/api/stockph/conceptdetail/%s/" % code
        headers = {"user-agent": "Mozilla/5.0 (Linux; Android 6.0.1; Moto G (4)) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.69 Mobile Safari/537.36 Edg/95.0.1020.44",
                   "cookie": "log=; spversion=20130314; reviewJump=nojump; searchGuide=sg; usersurvey=1; Hm_lvt_78c58f01938e4d85eaf619eae71b4ed1=1636527045,1636527397; historystock=601000%7C*%7C603906; Hm_lpvt_78c58f01938e4d85eaf619eae71b4ed1=1636536683; hxmPid=free_stock_gainian_000609; v=A4Par2Jf5mHE-qqVNtOQh_SKEkwt-Bc6UYxbbrVg3-JZdKx2PcinimFc67zG"}
        ths_tcgn_res = requests.get(url=ths_url, headers=headers)

        for jksd in ths_tcgn_res.json()['data']:
            if jksd['title'] in llo:
                continue
            ths_tcgn = ths_tcgn + ' ' + jksd['title']

        return ths_tcgn
    except Exception as e:
        print(e, code)
        return ths_tcgn


def get_record_datasad(ts, ts2):
    # ts = settings_db.find_one({"name": "nine_timestamp"})['timestamp']
    all = record_db.find({"ts": {"$gte": ts, "$lte": ts2}, 'p5': {
                         "$gte": 2}}).sort([('symbol', 1)])

    kk = {}
    for i in all:
        i.pop("_id")
        i['date'] = time.strftime('%Y-%m-%d %H:%M', time.localtime(i['ts']))
        if i['symbol'] not in kk:
            xxx = stock_db.find_one({"symbol": i['symbol']})
            i['name'] = xxx['name']
            # i['important'] = xxx['high']

            if not xxx.get('ths_hxtc'):
                print(i['symbol'])
                xxx['ths_hxtc'] = ths_res(i['symbol'][2:])
                stock_db.update_one({"symbol": i['symbol']}, {
                                    "$set": {"ths_hxtc": xxx['ths_hxtc']}}, upsert=True)
            i['tc'] = xxx['ths_hxtc']
            # print(i['symbol'])
            kk[i['symbol']] = []
        if len(kk[i['symbol']]) < 3:

            kk[i['symbol']].append(i)

            # print(i)
    count = 0
    count1 = 0
    p = {}
    for k, v in kk.items():
        if '09:35' <= v[0]['date'].split(" ")[1] <= '14:00':
            # print(k)
            count += 1
            if v[0].get("high"):
                count1 += 1
            for zz in v:

                # print(zz)
                pass
            topic = v[0]['tc'].split(" ")
            for jj in topic:
                if jj not in p:
                    p[jj] = []
                p[jj].append(k)

    l = []
    for k, v in p.items():
        concept_db.update_one({"concept": k, 'ts': ts}, {
                              "$set": {"num": len(v)}}, upsert=True)
        l.append([k, v])
    l = sorted(l, key=lambda x: len(x[1]), reverse=True)
    # for i in l:
    #     print(i[0], i[1], len(i[1]))

    print(count, count1)


def get_record_histroy():
    ts = settings_db.find_one({"name": "nine_timestamp"})['timestamp']
    all = record_db.find({"ts": {"$gte": ts}}).sort([('ts', 1)])

    kk = {}
    for i in all:
        i.pop("_id")
        i['date'] = time.strftime('%Y-%m-%d %H:%M', time.localtime(i['ts']))
        if i['symbol'] not in kk:
            xxx = stock_db.find_one({"symbol": i['symbol']})


def get_record_histroy1():
    # tss = settings_db.find_one({"name": "nine_timestamp"})['timestamp'] - 9 * 3600 - 1800
    st = data_day_db.find_one({'symbol': 'SH600302'})
    sttt = st['item'][-5:]
    column = ["C", "D", "E", "F", "G"]
    sheet_name_list = []
    f2 = Font(color="000079", b=True)
    f1 = Font(color="006000", b=True)
    for index in sttt[::-1]:
        ts = int(index[0] / 1000)
        all = record_db.find({"ts": {"$gte": ts, "$lte": ts + 3600 * 24},
                             'p5': {"$gte": 1}}).sort([('symbol', 1), ('ts', 1)])
        book_name = time.strftime('%Y-%m-%d', time.localtime(ts))
        book = Workbook()
        sheet = book.active
        kk = []
        fail = []
        sheet_name_list.append(book_name)
        start = ts + 9 * 3600 + 2100
        end = ts + 14 * 3600
        pp = []

        for i in all:
            if i['ts'] < start or i['ts'] > end:
                fail.append(i['symbol'])
                continue
            if i['symbol'] in fail:
                continue
            pp.append(i)
        for i in range(len(sheet_name_list)):
            sheet[f'{column[i]}1'] = sheet_name_list[len(sheet_name_list)-1-i]
        sheet[f'B1'] = '首日结果'
        sheet[f'H1'] = '合计结果'
        sheet[f'I1'] = '名称'
        sheet[f'J1'] = '买入时间'
        sheet[f'K1'] = '缩量日期'
        sheet[f'L1'] = '概念'

        for i in pp:
            if i['symbol'] not in kk:
                kk.append(i['symbol'])
                sheet[f'A{len(kk)+1}'] = i['symbol']
                stock = data_day_db.find_one({'symbol': i['symbol']})

                stttt = stock_db.find_one({'symbol': i['symbol']})
                ct = 0
                g = 0
                xx = ''
                high = i.get("high")
                if high == 2:
                    sheet.cell(len(kk)+1, 1).font = f2
                if high == 1:
                    sheet.cell(len(kk)+1, 1).font = f1

                sl = ''
                back = 0

                for ss in stock['item']:
                    if ss[0] < ts * 1000:
                        continue
                    if ss[0] == ts * 1000:
                        back = ss[9]
                        pppp = i.get('price')

                        if pppp:
                            xx = round((ss[5] - pppp) / pppp * 100, 2)
                        sheet[f'B{len(kk)+1}'] = xx

                        if high == 2:
                            sheet.cell(len(kk)+1, 2).font = f2
                        if high == 1:
                            sheet.cell(len(kk)+1, 2).font = f1

                    sheet[f'{column[ct]}{len(kk)+1}'] = ss[7]
                    if high == 2:
                        sheet.cell(len(kk)+1, ct+3).font = f2
                    if high == 1:
                        sheet.cell(len(kk)+1, ct+3).font = f1

                    if ct >= 1:
                        g += ss[7]
                        if not sl and back * 0.6 > ss[9]:
                            sl = 1
                            sheet.cell(len(kk)+1, ct +
                                       3).font = Font(color="AE8F00", b=True)
                            sheet[f'K{len(kk)+1}'] = time.strftime('%Y/%m/%d',
                                                                   time.localtime(int(ss[0] / 1000)))

                    ct += 1

                # sheet.row_dimensions[1].fill = green_fill
                sheet[f'H{len(kk)+1}'] = g + xx
                sheet[f'I{len(kk)+1}'] = stttt['name']
                sheet[f'J{len(kk)+1}'] = time.strftime('%Y/%m/%d %H:%M',
                                                       time.localtime(i['ts']))

                sheet[f'L{len(kk)+1}'] = stttt.get('ths_hxtc') if stttt.get(
                    'ths_hxtc') else stttt.get('hxtc')

        book.save(f"{book_name}.xlsx")


def get_record_histroy2():
    # tss = settings_db.find_one({"name": "nine_timestamp"})['timestamp'] - 9 * 3600 - 1800
    st = data_day_db.find_one({'symbol': 'SZ002466'})
    sttt = st['item'][-5:]
    column = ["C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", 'N']
    sheet_name_list = []
    f2 = Font(color="000079", b=True)
    f1 = Font(color="006000", b=True)
    llll = []
    sheet_name_list = [time.strftime(
        '%Y-%m-%d', time.localtime(int(x[0] / 1000))) for x in sttt]
    for index in sttt:
        ts = int(index[0] / 1000)
        all = record_db.find({"ts": {"$gte": ts, "$lte": ts + 3600 * 24},
                             'p5': {"$gte": 2}}).sort([('symbol', 1), ('ts', 1)])
        book_name = time.strftime('%Y-%m-%d', time.localtime(ts))
        book = Workbook()
        sheet = book.active
        kk = []
        fail = []
        # sheet_name_list.append(book_name)
        start = ts + 9 * 3600 + 2100
        end = ts + 14 * 3600
        pp = []

        for i in all:
            if i['ts'] < start or i['ts'] > end:
                fail.append(i['symbol'])
                continue
            if i['symbol'] in fail:
                continue
            pp.append(i)
        for i in range(len(sheet_name_list)):
            sheet[f'{column[i]}1'] = sheet_name_list[i]
        sheet_name_list = sheet_name_list[1:]
        sheet[f'B1'] = '首日结果'
        sheet[f'H1'] = '合计结果'
        sheet[f'I1'] = '名称'
        sheet[f'J1'] = '买入时间'
        # sheet[f'K1'] = '缩量日期'
        sheet[f'{column[-1]}1'] = '概念'
        sheet[f'{column[-2]}1'] = '日K量比'
        # sheet[f'{column[-3]}1'] = '当时K量比'

        for i in pp:
            if i['symbol'] not in kk:
                bkkk = 0
                stttt = stock_db.find_one({'symbol': i['symbol'], 'use': 1})

                if not stttt:
                    continue
                stock = data_day_db.find_one({'symbol': i['symbol']})
                stock_five = data_five_db.find_one({'symbol': i['symbol']})
                # #########################
                idx = 0
                for iss in range(len(stock['item'])):
                    if stock['item'][iss][0] == ts * 1000:
                        idx = iss
                ct1 = 0
                ct2 = 0
                ct3 = 0
                lasttt = 0
                flag = 8
                fu_flag = []
                ggg = 0
                for d in stock['item'][idx - 10: idx]:
                    if d[7] < -9:
                        ct1 += 10
                        ct2 += 10
                        ct3 += 10

                    if d[7] < 2:
                        ct1 += 1

                    if d[7] < 1:
                        ct2 += 1

                    if d[7] < 0.5:
                        ct3 += 1
                        fu_flag.append(d[7])
                    else:
                        fu_flag = []

                    if len(fu_flag) == 3:
                        ct1 += 10
                        ct2 += 10
                        ct3 += 10

                    if lasttt + d[7] < -10:
                        ct1 += 10
                        ct2 += 10
                        ct3 += 10
                    lasttt = d[7]
                    ggg += d[7]

                if ct3 > 5 or ggg > 10 or ggg < -10:
                    continue
                # #########################
                kk.append(i['symbol'])
                sheet[f'A{len(kk)+1}'] = i['symbol']

                ct = 0
                g = 0
                xx = ''
                high = i.get("high")

                if ct2 < 6:
                    sheet.cell(len(kk)+1, 1).font = f1

                if ct1 < 6:
                    sheet.cell(len(kk)+1, 1).font = f2

                if i['symbol'] in llll:
                    sheet.cell(
                        len(kk)+1, 1).font = Font(color="FF0000", b=True)
                llll.append(i['symbol'])
                sl = ''
                back = 0

                # five_sum = 0
                # for fffive in stock_five['item']:
                #     if ts*1000 < fffive[0]:
                #         if i['ts'] * 1000 >= fffive[0]:
                #             five_sum += fffive[9]
                #         else:
                #             five_sum += fffive[9]
                #             break

                # sheet[f'{column[-3]}{len(kk)+1}'] = round(five_sum / stock['item'][idx-1][9], 2)

                for ss in stock['item']:
                    if ct >= 5:
                        break
                    if ss[0] < ts * 1000:
                        continue
                    if ss[0] == ts * 1000:
                        back = ss[9]
                        pppp = i.get('price')

                        if pppp:
                            xx = round((ss[5] - pppp) / pppp * 100, 2)
                        sheet[f'B{len(kk)+1}'] = xx

                        if ct2 < 6:
                            sheet.cell(len(kk)+1, 2).font = f1

                        if ct1 < 6:
                            sheet.cell(len(kk)+1, 2).font = f2

                        sheet[f'{column[-2]}{len(kk)+1}'] = round(
                            ss[9] / stock['item'][idx-1][9], 2)
                        sheet[f'C{len(kk)+1}'] = ss[7]

                    sheet[f'{column[ct]}{len(kk)+1}'] = ss[7]

                    if ct2 < 6:
                        sheet.cell(len(kk)+1, ct+3).font = f1

                    if ct1 < 6:
                        sheet.cell(len(kk)+1, ct+3).font = f2

                    if ct >= 1:
                        g += ss[7]
                        # if not sl and back * 0.6 > ss[9]:
                        #     sl = 1
                        #     sheet.cell(len(kk)+1,ct+3).font = Font(color="AE8F00", b=True)
                        # sheet[f'K{len(kk)+1}'] = time.strftime('%Y/%m/%d', time.localtime(int(ss[0] / 1000)))

                    ct += 1

                # sheet.row_dimensions[1].fill = green_fill
                sheet[f'H{len(kk)+1}'] = g + xx if xx != '' else g
                sheet[f'I{len(kk)+1}'] = stttt['name']
                sheet[f'J{len(kk)+1}'] = time.strftime('%Y/%m/%d %H:%M',
                                                       time.localtime(i['ts']))

                sheet[f'{column[-1]}{len(kk)+1}'] = stttt.get(
                    'ths_hxtc') if stttt.get('ths_hxtc') else stttt.get('hxtc')
                if stttt['price'] <= 3000000000:
                    sheet.cell(
                        len(kk)+1, 14).font = Font(color="FF0000", b=True)

        book.save(f"{book_name}.xlsx")


def get_record_histroy3():
    # tss = settings_db.find_one({"name": "nine_timestamp"})['timestamp'] - 9 * 3600 - 1800
    st = data_day_db.find_one({'symbol': 'SZ002466'})
    sttt = st['item'][-5:]
    column = ["C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", 'N']
    sheet_name_list = []
    f2 = Font(color="000079", b=True)
    f1 = Font(color="006000", b=True)
    llll = []
    sheet_name_list = [time.strftime(
        '%Y-%m-%d', time.localtime(int(x[0] / 1000))) for x in sttt]
    for index in sttt:
        ts = int(index[0] / 1000)
        all = record_db.find({"ts": {"$gte": ts, "$lte": ts + 3600 * 24},
                             'p5': {"$gte": 2}}).sort([('symbol', 1), ('ts', 1)])
        book_name = time.strftime('%Y-%m-%d', time.localtime(ts))
        book = Workbook()
        sheet = book.active
        kk = []
        fail = []
        # sheet_name_list.append(book_name)
        start = ts + 9 * 3600 + 2100
        end = ts + 14 * 3600
        pp = []

        for i in all:
            if i['symbol'] == 'SH600032':
                print('1222222222222222')
            if i['ts'] < start or i['ts'] > end:
                fail.append(i['symbol'])
                continue
            if i['symbol'] in fail:
                continue
            pp.append(i)
        for i in range(len(sheet_name_list)):
            sheet[f'{column[i]}1'] = sheet_name_list[i]
        sheet_name_list = sheet_name_list[1:]
        sheet[f'B1'] = '首日结果'
        sheet[f'H1'] = '合计结果'
        sheet[f'I1'] = '名称'
        sheet[f'J1'] = '买入时间'
        # sheet[f'K1'] = '缩量日期'
        sheet[f'{column[-1]}1'] = '概念'
        sheet[f'{column[-2]}1'] = '日K量比'
        # sheet[f'{column[-3]}1'] = '当时K量比'

        for i in pp:
            if i['symbol'] not in kk:
                bkkk = 0
                stttt = stock_db.find_one({'symbol': i['symbol'], 'use': 1})

                if not stttt:
                    continue
                if stttt['symbol'] == 'SH600032':
                    print('1222222222222222')
                stock = data_day_db.find_one({'symbol': i['symbol']})
                stock_five = data_five_db.find_one({'symbol': i['symbol']})
                # #########################
                idx = 0
                for iss in range(len(stock['item'])):
                    if stock['item'][iss][0] == ts * 1000:
                        idx = iss

                lasttt = 0
                flag = 8
                fu_flag = []

                # #########################
                kk.append(i['symbol'])
                sheet[f'A{len(kk)+1}'] = i['symbol']

                ct = 0
                g = 0
                xx = ''
                high = i.get("high")

                if high == 2:
                    sheet.cell(len(kk)+1, 1).font = f1

                if high == 1:
                    sheet.cell(len(kk)+1, 1).font = f2

                if i['symbol'] in llll:
                    sheet.cell(
                        len(kk)+1, 1).font = Font(color="FF0000", b=True)
                llll.append(i['symbol'])
                sl = ''
                back = 0

                # five_sum = 0
                # for fffive in stock_five['item']:
                #     if ts*1000 < fffive[0]:
                #         if i['ts'] * 1000 >= fffive[0]:
                #             five_sum += fffive[9]
                #         else:
                #             five_sum += fffive[9]
                #             break

                # sheet[f'{column[-3]}{len(kk)+1}'] = round(five_sum / stock['item'][idx-1][9], 2)

                for ss in stock['item']:
                    if ss[0] < ts * 1000:
                        continue
                    if ss[0] == ts * 1000:
                        back = ss[9]
                        pppp = i.get('price')

                        if pppp:
                            xx = round((ss[5] - pppp) / pppp * 100, 2)
                        sheet[f'B{len(kk)+1}'] = xx

                        if high == 2:
                            sheet.cell(len(kk)+1, 2).font = f1

                        if high == 1:
                            sheet.cell(len(kk)+1, 2).font = f2

                        sheet[f'{column[-2]}{len(kk)+1}'] = round(
                            ss[9] / stock['item'][idx-1][9], 2)
                        sheet[f'C{len(kk)+1}'] = ss[7]

                    sheet[f'{column[ct]}{len(kk)+1}'] = ss[7]

                    if high == 2:
                        sheet.cell(len(kk)+1, ct+3).font = f1

                    if high == 1:
                        sheet.cell(len(kk)+1, ct+3).font = f2

                    if ct >= 1:
                        g += ss[7]
                        # if not sl and back * 0.6 > ss[9]:
                        #     sl = 1
                        #     sheet.cell(len(kk)+1,ct+3).font = Font(color="AE8F00", b=True)
                        # sheet[f'K{len(kk)+1}'] = time.strftime('%Y/%m/%d', time.localtime(int(ss[0] / 1000)))

                    ct += 1

                # sheet.row_dimensions[1].fill = green_fill
                sheet[f'H{len(kk)+1}'] = g + xx if xx != '' else g
                sheet[f'I{len(kk)+1}'] = stttt['name']
                sheet[f'J{len(kk)+1}'] = time.strftime('%Y/%m/%d %H:%M',
                                                       time.localtime(i['ts']))

                sheet[f'{column[-1]}{len(kk)+1}'] = stttt.get(
                    'ths_hxtc') if stttt.get('ths_hxtc') else stttt.get('hxtc')

        book.save(f"{book_name}.xlsx")


def get_record_histroy4():
    # tss = settings_db.find_one({"name": "nine_timestamp"})['timestamp'] - 9 * 3600 - 1800
    st = data_day_db.find_one({'symbol': 'SZ002466'})
    sttt = st['item'][-10:-5]
    column = ["C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", 'N']
    sheet_name_list = []
    f2 = Font(color="000079", b=True)
    f1 = Font(color="006000", b=True)

    sheet_name_list = [time.strftime(
        '%Y-%m-%d', time.localtime(int(x[0] / 1000))) for x in sttt]
    for index in sttt:
        ts = int(index[0] / 1000)
        all = record_db.find({"ts": {"$gte": ts, "$lte": ts + 3600 * 24},
                             'p5': {"$gte": 2}}).sort([('symbol', 1), ('ts', 1)])
        book_name = time.strftime('%Y-%m-%d', time.localtime(ts))
        book = Workbook()
        sheet = book.active
        kk = []
        fail = []
        # sheet_name_list.append(book_name)
        start = ts + 9 * 3600 + 2100
        end = ts + 14 * 3600
        pp = []

        for i in all:
            if i['ts'] < start or i['ts'] > end:
                fail.append(i['symbol'])
                continue
            if i['symbol'] in fail:
                continue
            pp.append(i)
        for i in range(len(sheet_name_list)):
            sheet[f'{column[i]}1'] = sheet_name_list[i]
        sheet_name_list = sheet_name_list[1:]
        sheet[f'B1'] = '首日结果'
        sheet[f'H1'] = '合计结果'
        sheet[f'I1'] = '名称'
        sheet[f'J1'] = '买入时间'
        # sheet[f'K1'] = '缩量日期'
        sheet[f'{column[-1]}1'] = '概念'
        sheet[f'{column[-2]}1'] = '日K量比'
        # sheet[f'{column[-3]}1'] = '当时K量比'
        llll = []
        for i in pp:
            if i['symbol'] not in kk:
                bkkk = 0
                stttt = stock_db.find_one({'symbol': i['symbol'], 'use': 1})

                if not stttt:
                    continue
                if i['symbol'] in llll:
                    continue
                stock = data_day_db.find_one({'symbol': i['symbol']})
                
                stock_five = data_five_db.find_one({'symbol': i['symbol']})
                # #########################
                idx = 0
                for iss in range(len(stock['item'])):
                    if stock['item'][iss][0] == ts * 1000:
                        idx = iss
                # if len(stock['item']) > idx+1:
                #     print(i['symbol'])
                #     if stock['item'][idx+1][7] < -3:
                #         continue
                # add_oneone = 0
                # cyyy = 60
                # for oenone in stock_one['item']:
                #     if oenone[0] < i['ts'] * 1000:
                #         continue
                #     if cyyy <= 0:
                #         break
                #     add_oneone += oenone[9]
                #     cyyy -= 1

                # bili = round(add_oneone / stock['item'][idx-1][9], 2)
                bili = 0
                # print(i['symbol'], add_oneone, stock['item'][idx-1][9])

                flags = EMA([round(decimal.Decimal(x[5]), 2)
                            for x in stock['item'][:idx]])

                # if not flags:
                #     print(book_name, i['symbol'])
                #     llll.append(i['symbol'])
                #     continue
                lasttt = 0
                flag = 8
                fu_flag = []

                # #########################
                kk.append(i['symbol'])

                ct = 0
                g = 0
                xx = ''
                high = i.get("high")

                if high == 2:
                    sheet.cell(len(kk)+1, 1).font = f1

                if high == 1:
                    sheet.cell(len(kk)+1, 1).font = f2

                if i['symbol'] in llll:
                    sheet.cell(
                        len(kk)+1, 1).font = Font(color="FF0000", b=True)
                llll.append(i['symbol'])
                sl = ''
                back = 0

                # five_sum = 0
                # for fffive in stock_five['item']:
                #     if ts*1000 < fffive[0]:
                #         if i['ts'] * 1000 >= fffive[0]:
                #             five_sum += fffive[9]
                #         else:
                #             five_sum += fffive[9]
                #             break

                # sheet[f'{column[-3]}{len(kk)+1}'] = round(five_sum / stock['item'][idx-1][9], 2)

                for ss in stock['item']:
                    if ct >= 5:
                        break
                    if ss[0] < ts * 1000:
                        continue
                    if ss[0] == ts * 1000:
                        back = ss[9]
                        pppp = i.get('price')

                        if pppp:
                            xx = round((ss[5] - pppp) / pppp * 100, 2)
                        sheet[f'B{len(kk)+1}'] = xx

                        if high == 2:
                            sheet.cell(len(kk)+1, 2).font = f1

                        if high == 1:
                            sheet.cell(len(kk)+1, 2).font = f2

                        sheet[f'{column[-2]}{len(kk)+1}'] = round(
                            ss[9] / stock['item'][idx-1][9], 2)
                        sheet[f'C{len(kk)+1}'] = ss[7]

                    # sheet[f'{column[ct]}{len(kk)+1}'] = ss[7]

                    if high == 2:
                        sheet.cell(len(kk)+1, ct+3).font = f1

                    if high == 1:
                        sheet.cell(len(kk)+1, ct+3).font = f2

                    if ct >= 1:
                        g += ss[7]
                        # if not sl and back * 0.6 > ss[9]:
                        #     sl = 1
                        #     sheet.cell(len(kk)+1,ct+3).font = Font(color="AE8F00", b=True)
                        # sheet[f'K{len(kk)+1}'] = time.strftime('%Y/%m/%d', time.localtime(int(ss[0] / 1000)))

                    ct += 1

                # sheet.row_dimensions[1].fill = green_fill
                sheet[f'H{len(kk)+1}'] = g + xx if xx != '' else g
                sheet[f'I{len(kk)+1}'] = stttt['name']
                sheet[f'J{len(kk)+1}'] = time.strftime('%Y/%m/%d %H:%M',
                                                       time.localtime(i['ts']))

                sheet[f'{column[-1]}{len(kk)+1}'] = stttt.get(
                    'ths_hxtc') if stttt.get('ths_hxtc') else stttt.get('hxtc')
                sheet[f'A{len(kk)+1}'] = i['symbol']
                sheet[f'K{len(kk)+1}'] = flags
                sheet[f'L{len(kk)+1}'] = bili

        book.save(f"{book_name}.xlsx")


def get_record_histroy5():
    # tss = settings_db.find_one({"name": "nine_timestamp"})['timestamp'] - 9 * 3600 - 1800
    st = data_day_db.find_one({'symbol': 'SZ002466'})
    sttt = st['item'][-5:]
    column = ["C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", 'N']
    sheet_name_list = []
    f2 = Font(color="000079", b=True)
    f1 = Font(color="006000", b=True)

    sheet_name_list = [time.strftime(
        '%Y-%m-%d', time.localtime(int(x[0] / 1000))) for x in sttt]
    for index in sttt:
        ts = int(index[0] / 1000)
        all = record_main_db.find({"ts": {"$gte": ts, "$lte": ts + 3600 * 24}}).sort([('symbol', 1), ('ts', 1)])
        book_name = time.strftime('%Y-%m-%d', time.localtime(ts))
        book = Workbook()
        sheet = book.active
        kk = []
        fail = []
        # sheet_name_list.append(book_name)
        start = ts + 9 * 3600 + 2100
        end = ts + 14 * 3600
        pp = []

        for i in all:
            pp.append(i)
        for i in range(len(sheet_name_list)):
            sheet[f'{column[i]}1'] = sheet_name_list[i]
        sheet_name_list = sheet_name_list[1:]
        sheet[f'B1'] = '首日结果'
        sheet[f'H1'] = '合计结果'
        sheet[f'I1'] = '名称'
        sheet[f'J1'] = '买入时间'
        # sheet[f'K1'] = '缩量日期'
        sheet[f'{column[-1]}1'] = '概念'
        sheet[f'{column[-2]}1'] = '日K量比'
        # sheet[f'{column[-3]}1'] = '当时K量比'

        for i in pp:
            if i['symbol'] not in kk:
                stttt = stock_db.find_one({'symbol': i['symbol'], 'use': 1})


                stock = data_day_db.find_one({'symbol': i['symbol']})
                
                # #########################
                idx = 0
                for iss in range(len(stock['item'])):
                    if stock['item'][iss][0] == ts * 1000:
                        idx = iss
                # if len(stock['item']) > idx+1:
                #     print(i['symbol'])
                #     if stock['item'][idx+1][7] < -3:
                #         continue
                # add_oneone = 0
                # cyyy = 60
                # for oenone in stock_one['item']:
                #     if oenone[0] < i['ts'] * 1000:
                #         continue
                #     if cyyy <= 0:
                #         break
                #     add_oneone += oenone[9]
                #     cyyy -= 1

                # bili = round(add_oneone / stock['item'][idx-1][9], 2)
                bili = 0
                # print(i['symbol'], add_oneone, stock['item'][idx-1][9])

                # flags = EMA([round(decimal.Decimal(x[5]), 2)
                #             for x in stock['item'][:idx]])

                # if not flags:
                #     print(book_name, i['symbol'])
                #     llll.append(i['symbol'])
                #     continue
                lasttt = 0
                flag = 8
                fu_flag = []

                # #########################
                kk.append(i['symbol'])

                ct = 0
                g = 0
                xx = ''
                high = i.get("high")

                if high == 2:
                    sheet.cell(len(kk)+1, 1).font = f1

                if high == 1:
                    sheet.cell(len(kk)+1, 1).font = f2

                
                sl = ''
                back = 0

                # five_sum = 0
                # for fffive in stock_five['item']:
                #     if ts*1000 < fffive[0]:
                #         if i['ts'] * 1000 >= fffive[0]:
                #             five_sum += fffive[9]
                #         else:
                #             five_sum += fffive[9]
                #             break

                # sheet[f'{column[-3]}{len(kk)+1}'] = round(five_sum / stock['item'][idx-1][9], 2)

                for ss in stock['item']:
                    if ct >= 5:
                        break
                    if ss[0] < ts * 1000:
                        continue
                    if ss[0] == ts * 1000:
                        back = ss[9]
                        pppp = i['sons'][0].get('price')

                        if pppp:
                            xx = round((ss[5] - pppp) / pppp * 100, 2)
                        sheet[f'B{len(kk)+1}'] = xx

                        if high == 2:
                            sheet.cell(len(kk)+1, 2).font = f1

                        if high == 1:
                            sheet.cell(len(kk)+1, 2).font = f2

                        sheet[f'{column[-2]}{len(kk)+1}'] = round(
                            ss[9] / stock['item'][idx-1][9], 2)
                        sheet[f'C{len(kk)+1}'] = ss[7]

                    # sheet[f'{column[ct]}{len(kk)+1}'] = ss[7]

                    if high == 2:
                        sheet.cell(len(kk)+1, ct+3).font = f1

                    if high == 1:
                        sheet.cell(len(kk)+1, ct+3).font = f2

                    if ct >= 1:
                        g += ss[7]
                        # if not sl and back * 0.6 > ss[9]:
                        #     sl = 1
                        #     sheet.cell(len(kk)+1,ct+3).font = Font(color="AE8F00", b=True)
                        # sheet[f'K{len(kk)+1}'] = time.strftime('%Y/%m/%d', time.localtime(int(ss[0] / 1000)))

                    ct += 1

                # sheet.row_dimensions[1].fill = green_fill
                sheet[f'H{len(kk)+1}'] = g + xx if xx != '' else g
                sheet[f'I{len(kk)+1}'] = stttt['name']
                sheet[f'J{len(kk)+1}'] = time.strftime('%Y/%m/%d %H:%M',
                                                       time.localtime(i['ts']))

                sheet[f'{column[-1]}{len(kk)+1}'] = stttt.get(
                    'ths_hxtc') if stttt.get('ths_hxtc') else stttt.get('hxtc')
                sheet[f'A{len(kk)+1}'] = i['symbol']
                sheet[f'K{len(kk)+1}'] = 1
                sheet[f'L{len(kk)+1}'] = bili

        book.save(f"{book_name}.xlsx")


def get_record_histroy4_collection():
    # tss = settings_db.find_one({"name": "nine_timestamp"})['timestamp'] - 9 * 3600 - 1800
    st = data_day_db.find_one({'symbol': 'SZ002466'})
    sttt = st['item'][-5:]
    column = ["C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", 'N']
    sheet_name_list = []
    f2 = Font(color="000079", b=True)
    f1 = Font(color="006000", b=True)

    sheet_name_list = [time.strftime(
        '%Y-%m-%d', time.localtime(int(x[0] / 1000))) for x in sttt]
    for index in sttt:
        ts = int(index[0] / 1000)
        all = record_db.find({"ts": {"$gte": ts, "$lte": ts + 3600 * 24},
                             'p5': {"$gte": 2}}).sort([('symbol', 1), ('ts', 1)])

        book_name = time.strftime('%Y-%m-%d', time.localtime(ts))
        book = Workbook()
        sheet = book.active
        kk = []
        fail = []
        # sheet_name_list.append(book_name)
        start = ts + 9 * 3600 + 2100
        end = ts + 14 * 3600
        pp = []

        for i in all:
           
            if i['ts'] < start or i['ts'] > end:
                fail.append(i['symbol'])
                continue
            col = record_collect_db.find_one({"symbol": i['symbol']})
            if not col:
                fail.append(i['symbol'])
                continue
            if i['symbol'] in fail:
                continue
            
            pp.append(i)
        for i in range(len(sheet_name_list)):
            sheet[f'{column[i]}1'] = sheet_name_list[i]
        sheet_name_list = sheet_name_list[1:]
        sheet[f'B1'] = '首日结果'
        sheet[f'H1'] = '合计结果'
        sheet[f'I1'] = '名称'
        sheet[f'J1'] = '买入时间'
        # sheet[f'K1'] = '缩量日期'
        sheet[f'{column[-1]}1'] = '概念'
        sheet[f'{column[-2]}1'] = '日K量比'
        # sheet[f'{column[-3]}1'] = '当时K量比'
        llll = []
        for i in pp:
            if i['symbol'] not in kk:
                bkkk = 0
                stttt = stock_db.find_one({'symbol': i['symbol'], 'use': 1})

                if not stttt:
                    continue
                if i['symbol'] in llll:
                    continue
                stock = data_day_db.find_one({'symbol': i['symbol']})
                stock_one = data_one_db.find_one({'symbol': i['symbol']})
                stock_five = data_five_db.find_one({'symbol': i['symbol']})
                # #########################
                idx = 0
                for iss in range(len(stock['item'])):
                    if stock['item'][iss][0] == ts * 1000:
                        idx = iss
                # if len(stock['item']) > idx+1:
                #     print(i['symbol'])
                #     if stock['item'][idx+1][7] < -3:
                #         continue
                add_oneone = 0
                cyyy = 60
                for oenone in stock_one['item']:
                    if oenone[0] < i['ts'] * 1000:
                        continue
                    if cyyy <= 0:
                        break
                    add_oneone += oenone[9]
                    cyyy -= 1

                bili = round(add_oneone / stock['item'][idx-1][9], 2)
                # print(i['symbol'], add_oneone, stock['item'][idx-1][9])

                flags = EMA([round(decimal.Decimal(x[5]), 2)
                            for x in stock['item'][:idx]])

                # if not flags:
                #     print(book_name, i['symbol'])
                #     llll.append(i['symbol'])
                #     continue
                lasttt = 0
                flag = 8
                fu_flag = []

                # #########################
                kk.append(i['symbol'])

                ct = 0
                g = 0
                xx = ''
                high = i.get("high")

                if high == 2:
                    sheet.cell(len(kk)+1, 1).font = f1

                if high == 1:
                    sheet.cell(len(kk)+1, 1).font = f2

                if i['symbol'] in llll:
                    sheet.cell(
                        len(kk)+1, 1).font = Font(color="FF0000", b=True)
                llll.append(i['symbol'])
                sl = ''
                back = 0

                # five_sum = 0
                # for fffive in stock_five['item']:
                #     if ts*1000 < fffive[0]:
                #         if i['ts'] * 1000 >= fffive[0]:
                #             five_sum += fffive[9]
                #         else:
                #             five_sum += fffive[9]
                #             break

                # sheet[f'{column[-3]}{len(kk)+1}'] = round(five_sum / stock['item'][idx-1][9], 2)

                for ss in stock['item']:
                    if ct >= 5:
                        break
                    if ss[0] < ts * 1000:
                        continue
                    if ss[0] == ts * 1000:
                        back = ss[9]
                        pppp = i.get('price')

                        if pppp:
                            xx = round((ss[5] - pppp) / pppp * 100, 2)
                        sheet[f'B{len(kk)+1}'] = xx

                        if high == 2:
                            sheet.cell(len(kk)+1, 2).font = f1

                        if high == 1:
                            sheet.cell(len(kk)+1, 2).font = f2

                        sheet[f'{column[-2]}{len(kk)+1}'] = round(
                            ss[9] / stock['item'][idx-1][9], 2)
                        sheet[f'C{len(kk)+1}'] = ss[7]

                    sheet[f'{column[ct]}{len(kk)+1}'] = ss[7]

                    if high == 2:
                        sheet.cell(len(kk)+1, ct+3).font = f1

                    if high == 1:
                        sheet.cell(len(kk)+1, ct+3).font = f2

                    if ct >= 1:
                        g += ss[7]
                        # if not sl and back * 0.6 > ss[9]:
                        #     sl = 1
                        #     sheet.cell(len(kk)+1,ct+3).font = Font(color="AE8F00", b=True)
                        # sheet[f'K{len(kk)+1}'] = time.strftime('%Y/%m/%d', time.localtime(int(ss[0] / 1000)))

                    ct += 1

                # sheet.row_dimensions[1].fill = green_fill
                sheet[f'H{len(kk)+1}'] = g + xx if xx != '' else g
                sheet[f'I{len(kk)+1}'] = stttt['name']
                sheet[f'J{len(kk)+1}'] = time.strftime('%Y/%m/%d %H:%M',
                                                       time.localtime(i['ts']))

                sheet[f'{column[-1]}{len(kk)+1}'] = stttt.get(
                    'ths_hxtc') if stttt.get('ths_hxtc') else stttt.get('hxtc')
                sheet[f'A{len(kk)+1}'] = i['symbol']
                sheet[f'K{len(kk)+1}'] = flags
                sheet[f'L{len(kk)+1}'] = bili

        book.save(f"{book_name}.xlsx")


def EMA(arr):
    if len(arr) == 0:
        return -2
    rate12 = decimal.Decimal(2/13)

    data12 = [arr[0]]
    for i in range(1, len(arr)):
        p = rate12*arr[i] + (1-rate12)*data12[-1]
        data12.append(p)

    rate26 = decimal.Decimal(2/27)
    data26 = [arr[0]]
    for i in range(1, len(arr)):
        p = rate26*arr[i] + (1-rate26)*data26[-1]
        data26.append(p)

    dea = [data12[0]-data26[0]]
    diff = [data12[0]-data26[0]]
    for i in range(1, len(data26)):
        diff.append(data12[i]-data26[i])
        p = decimal.Decimal(0.2)*(diff[i]) + decimal.Decimal(0.8)*dea[-1]
        dea.append(p)

    flag = -2
    if round(round((diff[-1] - dea[-1]) * 1000) / 500, 2) > 0:
        fuck = 2
        last = 0
        macd = 100
        back = 100
        cctt = 1
        for i in range(len(diff)-1, len(diff)-20, -1):
            if last > 7:
                break
            t = round(round((diff[i] - dea[i]) * 1000) / 500, 2)
            if t <= 0:
                break
            t = round(round((diff[i] - dea[i]) * 1000) / 500, 2)
            if macd == 100:
                macd = t
                back = macd
            else:
                if t > macd:
                    if t - back >= 0.04:
                        return -2
                    else:
                        if t > back:
                            back = t
                            if fuck:
                                last -= 2
                                fuck -= 1

                macd = t
            last += 1
        if last > 7:
            return -2
        return last
    if round(round((diff[-1] - dea[-1]) * 1000) / 500, 2) == 0:
        return 0

    if round(round((diff[-1] - dea[-1]) * 1000) / 500, 2) >= -0.04:
        return -1

    return flag
